#import <UIKit/UIKit.h>

//数据库字段类型
#define    DB_TEXT      @"TEXT DEFAULT '' NOT NULL"
#define    DB_INTEGER   @"INTEGER DEFAULT 0 NOT NULL"
#define    DB_FOLAT     @"REAL DEFAULT 0.0 NOT NULL"
#define    DB_TIME      @"TIME"

@class MRRecordset;

//数据库 ============================================
@interface MRDatabase : NSObject

- (instancetype)initWithFile:(NSString *)filename;

@property (nonatomic, readonly) NSDateFormatter     *dateFormatter;

- (BOOL)open;
- (void)close;

- (void)begin;
- (void)commit;

- (uint)lastInsert;

//表操作
- (BOOL)exec:(NSString*)sql, ...;

- (BOOL)createTable:(NSString *)table fields:(NSArray *)fields;			//插入表
- (BOOL)deleteTable:(NSString *)table;

- (BOOL)insertToTable:(NSString *)table values:(NSDictionary *)values;			//插入表数据
- (BOOL)insertToTable:(NSString *)table values:(NSDictionary *)values notExists:(NSString *)where, ...;		//不符合指定条件插入表数据

- (BOOL)updateToTable:(NSString *)table values:(NSDictionary *)values;			//类似于Replace，但不同的是存在记录只是更新相关字段
- (BOOL)replaceToTable:(NSString *)table values:(NSDictionary *)values;			//替换记录，存在时对整条记录替换，没指定的字段用默认值替换

- (BOOL)updateForTable:(NSString *)table values:(NSDictionary *)values where:(NSString *)where, ...;		//按条件更新表数据
- (BOOL)deleteForTable:(NSString *)table where:(NSString *)where, ...;			//按条件删除表数据

//查询
- (MRRecordset *)query:(NSString *)sql, ...;

//常规方式查询（通过 MRRecordset 的 next 方法来读取记录，当next方法返回NO时记录结束）
- (MRRecordset *)selectForTable:(NSString *)table fields:(NSString *)fields group:(NSString *)group order:(NSString *)order where:(NSString *)where, ...;
- (MRRecordset *)selectForTable:(NSString *)table fields:(NSString *)fields where:(NSString *)where, ...;
- (MRRecordset *)selectForTable:(NSString *)table order:(NSString *)order where:(NSString *)where, ...;
- (MRRecordset *)selectForTable:(NSString *)table where:(NSString *)where, ...;

//列表方式查询（记录并缓存，可知道记录条数，并可通过 position 属性定位到需要的位置）
- (MRRecordset *)listForTable:(NSString *)table fields:(NSString *)fields group:(NSString *)group order:(NSString *)order where:(NSString *)where, ...;
- (MRRecordset *)listForTable:(NSString *)table fields:(NSString *)fields where:(NSString *)where, ...;
- (MRRecordset *)listForTable:(NSString *)table order:(NSString *)order where:(NSString *)where, ...;
- (MRRecordset *)listForTable:(NSString *)table where:(NSString *)where, ...;

@end

//记录集 ============================================
@interface MRRecordset : NSObject

- (instancetype)initWithDBase:(MRDatabase *)dbase query:(NSString *)query;

@property (nonatomic, readonly) NSString        *query;
@property (nonatomic, readonly) NSArray <NSString *>	*columns;		//当前查询结果的列名

@property (nonatomic, readonly) NSInteger       count;			//当前查询记录数（限list查询）
@property (nonatomic)			NSInteger       position;		//定位索引，从0开始对应数据记录索引（限list查询）

- (NSMutableDictionary *)dictionary;       //获取当前记录的字典集
- (NSMutableDictionary *)dictionaryAtIndex:(NSUInteger)index;

- (void)close;
- (BOOL)next;           //下条记录

- (int)intForColumn:(NSString *)name;
- (int)intForColumnIndex:(NSUInteger)index;
- (uint)uintForColumn:(NSString *)name;
- (uint)uintForColumnIndex:(NSUInteger)index;

- (double)doubleForColumn:(NSString *)name;
- (double)doubleForColumnIndex:(NSUInteger)index;

- (NSString *)stringForColumn:(NSString *)name;
- (NSString *)stringForColumnIndex:(NSUInteger)index;

- (NSDate *)dateForColumn:(NSString *)name;
- (NSDate *)dateForColumnIndex:(NSUInteger)index;

@end
